# -*- coding: utf - 8 -*-
"""
MySQL到KWDB语法转换器

负责将MySQL的SQL语法转换为KWDB兼容的语法，包括数据类型映射、语法适配等。
"""

import re
import logging
from typing import Dict, List, Optional, Any
try:
    from dataclasses import dataclass
except ImportError:
    # Python < 3.7 fallback
    def dataclass(cls):
        return cls

try:
    from .parser import ParsedStatement, StatementType
except ImportError:
    from parser import ParsedStatement, StatementType


@dataclass
class ConversionRule:
    """转换规则"""
    pattern: str
    replacement: str
    flags: int = re.IGNORECASE


class MySQLToKWDBConverter:
    """MySQL到KWDB语法转换器"""

    def __init__(self, config: Optional[Dict[str, Any]] = None):
        self.logger = logging.getLogger(__name__)
        self.config = config or {}

        # 默认数据类型映射
        self.default_type_mapping = {
            'VARCHAR': 'STRING',
            'CHAR': 'STRING',
            'TEXT': 'STRING',
            'LONGTEXT': 'STRING',
            'MEDIUMTEXT': 'STRING',
            'TINYTEXT': 'STRING',
            'INT': 'INT',
            'INTEGER': 'INT',
            'BIGINT': 'INT',
            'SMALLINT': 'INT',
            'TINYINT': 'INT',
            'DATETIME': 'TIMESTAMP',
            'TIMESTAMP': 'TIMESTAMP',
            'DATE': 'DATE',
            'TIME': 'TIME',
            'DECIMAL': 'DECIMAL',
            'NUMERIC': 'DECIMAL',
            'FLOAT': 'FLOAT',
            'DOUBLE': 'FLOAT',
            'REAL': 'FLOAT',
            'BOOLEAN': 'BOOL',
            'BOOL': 'BOOL',
            'BIT': 'BOOL',
            'BLOB': 'BYTES',
            'LONGBLOB': 'BYTES',
            'MEDIUMBLOB': 'BYTES',
            'TINYBLOB': 'BYTES',
            'BINARY': 'BYTES',
            'VARBINARY': 'BYTES',
            'JSON': 'JSONB',
            'ENUM': 'STRING',
            'SET': 'STRING'
        }

        # 合并用户自定义类型映射
        self.type_mapping = {**self.default_type_mapping}
        if 'type_mapping' in self.config:
            self.type_mapping.update(self.config['type_mapping'])

        # 默认语法转换规则
        self.default_syntax_rules = [
            ConversionRule(r'AUTO_INCREMENT', 'DEFAULT unique_rowid()'),
            ConversionRule(r'ENGINE\s*=\s*\w+', ''),
            ConversionRule(r'DEFAULT\s+CHARSET\s*=\s*\w+', ''),
            ConversionRule(r'COLLATE\s*=\s*\w+', ''),
            ConversionRule(r'COLLATE\s+\w+', ''),  # 匹配列定义中的COLLATE
            ConversionRule(r'CHARACTER\s+SET\s+\w+', ''),
            ConversionRule(r'DEFAULT\s+CHARACTER\s+SET\s+\w+', ''),
            ConversionRule(r'COMMENT\s*=\s*[\'"][^\'"]*[\'"]', ''),
            ConversionRule(r'COMMENT\s+[\'"][^\'"]*[\'"]', ''),  # 列定义中的COMMENT
            ConversionRule(r'`([^`]+)`', r'\1'),  # 移除反引号
            ConversionRule(r'IF\s+NOT\s+EXISTS', 'IF NOT EXISTS'),
            ConversionRule(r'DROP\s+TABLE\s+IF\s+EXISTS', 'DROP TABLE IF EXISTS'),
            ConversionRule(r'TINYINT\s*\(\s*1\s*\)', 'BOOL'),  # TINYINT(1) -> BOOL
            ConversionRule(r'UNSIGNED', ''),  # 移除UNSIGNED关键字
            ConversionRule(r'ZEROFILL', ''),  # 移除ZEROFILL关键字
        ]

        # 合并用户自定义语法规则
        self.syntax_rules = self.default_syntax_rules.copy()
        if 'syntax_rules' in self.config:
            for pattern, replacement in self.config['syntax_rules'].items():
                self.syntax_rules.append(ConversionRule(pattern, replacement))

    def convert_statements(self, statements: List[ParsedStatement]) -> List[ParsedStatement]:
        """
        转换语句列表

        Args:
            statements: 解析后的语句列表

        Returns:
            转换后的语句列表
        """
        converted_statements = []

        for stmt in statements:
            try:
                converted_stmt = self.convert_statement(stmt)
                if converted_stmt:
                    converted_statements.append(converted_stmt)
            except Exception as e:
                self.logger.error(f"转换语句时出错: {e}")
                self.logger.debug(f"问题语句: {stmt.original_sql[:100]}...")

        self.logger.info(f"转换完成，共处理{len(converted_statements)}个语句")
        return converted_statements

    def convert_statement(self, stmt: ParsedStatement) -> Optional[ParsedStatement]:
        """
        转换单个语句

        Args:
            stmt: 原始语句

        Returns:
            转换后的语句
        """
        if stmt.statement_type == StatementType.CREATE_TABLE:
            return self._convert_create_table(stmt)
        elif stmt.statement_type == StatementType.INSERT_INTO:
            return self._convert_insert_into(stmt)
        elif stmt.statement_type == StatementType.CREATE_DATABASE:
            return self._convert_create_database(stmt)
        elif stmt.statement_type == StatementType.DROP_DATABASE:
            return self._convert_drop_database(stmt)
        elif stmt.statement_type == StatementType.DROP_TABLE:
            return self._convert_drop_table(stmt)
        elif stmt.statement_type == StatementType.CREATE_INDEX:
            return self._convert_create_index(stmt)
        else:
            # 对于其他类型的语句，进行基本的语法转换
            return self._apply_basic_conversion(stmt)

    def _convert_create_table(self, stmt: ParsedStatement) -> ParsedStatement:
        """转换CREATE TABLE语句"""
        sql = stmt.original_sql

        # 应用基本语法转换规则
        sql = self._apply_syntax_rules(sql)

        # 转换数据类型
        sql = self._convert_data_types(sql)

        # 处理主键定义
        sql = self._convert_primary_key(sql)

        # 处理索引定义
        sql = self._convert_table_indexes(sql)

        # 处理约束
        sql = self._convert_constraints(sql)

        # 清理多余的空白和逗号
        sql = self._cleanup_sql(sql)

        # 创建新的语句对象
        new_stmt = ParsedStatement(
            statement_type=stmt.statement_type,
            original_sql=sql,
            parsed_sql=stmt.parsed_sql,
            table_name=stmt.table_name,
            database_name=stmt.database_name,
            columns=stmt.columns,
            indexes=stmt.indexes,
            constraints=stmt.constraints
        )

        return new_stmt

    def _convert_insert_into(self, stmt: ParsedStatement) -> ParsedStatement:
        """转换INSERT INTO语句"""
        sql = stmt.original_sql

        # 移除反引号
        sql = re.sub(r'`([^`]+)`', r'\1', sql)

        # 处理ON DUPLICATE KEY UPDATE（KWDB不支持，需要转换为UPSERT）
        if 'ON DUPLICATE KEY UPDATE' in sql.upper():
            sql = self._convert_on_duplicate_key(sql)

        new_stmt = ParsedStatement(
            statement_type=stmt.statement_type,
            original_sql=sql,
            parsed_sql=stmt.parsed_sql,
            table_name=stmt.table_name
        )

        return new_stmt

    def _convert_create_database(self, stmt: ParsedStatement) -> ParsedStatement:
        """转换CREATE DATABASE语句"""
        sql = stmt.original_sql

        # 移除MySQL特有的字符集和排序规则
        sql = re.sub(r'DEFAULT\s+CHARACTER\s+SET\s+\w+', '', sql, flags=re.IGNORECASE)
        sql = re.sub(r'COLLATE\s+\w+', '', sql, flags=re.IGNORECASE)
        sql = re.sub(r'`([^`]+)`', r'\1', sql)

        sql = self._cleanup_sql(sql)

        new_stmt = ParsedStatement(
            statement_type=stmt.statement_type,
            original_sql=sql,
            parsed_sql=stmt.parsed_sql,
            database_name=stmt.database_name
        )

        return new_stmt

    def _convert_drop_database(self, stmt: ParsedStatement) -> ParsedStatement:
        """转换DROP DATABASE语句"""
        sql = re.sub(r'`([^`]+)`', r'\1', stmt.original_sql)

        new_stmt = ParsedStatement(
            statement_type=stmt.statement_type,
            original_sql=sql,
            parsed_sql=stmt.parsed_sql,
            database_name=stmt.database_name
        )

        return new_stmt

    def _convert_drop_table(self, stmt: ParsedStatement) -> ParsedStatement:
        """转换DROP TABLE语句"""
        sql = re.sub(r'`([^`]+)`', r'\1', stmt.original_sql)

        new_stmt = ParsedStatement(
            statement_type=stmt.statement_type,
            original_sql=sql,
            parsed_sql=stmt.parsed_sql,
            table_name=stmt.table_name
        )

        return new_stmt

    def _convert_create_index(self, stmt: ParsedStatement) -> ParsedStatement:
        """转换CREATE INDEX语句"""
        sql = stmt.original_sql

        # 移除反引号
        sql = re.sub(r'`([^`]+)`', r'\1', sql)

        # 移除MySQL特有的索引选项
        sql = re.sub(r'USING\s+\w+', '', sql, flags=re.IGNORECASE)
        sql = re.sub(r'KEY_BLOCK_SIZE\s*=\s*\d+', '', sql, flags=re.IGNORECASE)

        sql = self._cleanup_sql(sql)

        new_stmt = ParsedStatement(
            statement_type=stmt.statement_type,
            original_sql=sql,
            parsed_sql=stmt.parsed_sql,
            table_name=stmt.table_name
        )

        return new_stmt

    def _apply_basic_conversion(self, stmt: ParsedStatement) -> ParsedStatement:
        """应用基本转换规则"""
        sql = self._apply_syntax_rules(stmt.original_sql)

        new_stmt = ParsedStatement(
            statement_type=stmt.statement_type,
            original_sql=sql,
            parsed_sql=stmt.parsed_sql,
            table_name=stmt.table_name,
            database_name=stmt.database_name
        )

        return new_stmt

    def _apply_syntax_rules(self, sql: str) -> str:
        """应用语法转换规则"""
        for rule in self.syntax_rules:
            sql = re.sub(rule.pattern, rule.replacement, sql, flags=rule.flags)
        return sql

    def _convert_data_types(self, sql: str) -> str:
        """转换数据类型"""
        # 处理带长度的数据类型，如VARCHAR(255)
        def replace_type_with_length(match):
            mysql_type = match.group(1).upper()
            length = match.group(2)

            if mysql_type in self.type_mapping:
                kwdb_type = self.type_mapping[mysql_type]
                # 某些KWDB类型不需要长度参数
                if kwdb_type in ['STRING', 'BYTES']:
                    return f"{kwdb_type}({length})"
                else:
                    return kwdb_type
            return match.group(0)

        # 匹配类型(长度)的模式，但排除函数调用如unique_rowid()
        sql = re.sub(r'(?<!DEFAULT\s)(\w+)\s*\(\s*(\d+(?:\s*,\s*\d+)?)\s*\)',
                     replace_type_with_length, sql, flags=re.IGNORECASE)

        # 处理不带长度的数据类型
        for mysql_type, kwdb_type in self.type_mapping.items():
            pattern = r'\b' + re.escape(mysql_type) + r'\b'
            sql = re.sub(pattern, kwdb_type, sql, flags=re.IGNORECASE)

        return sql

    def _convert_primary_key(self, sql: str) -> str:
        """转换主键定义"""
        # KWDB的主键语法与MySQL基本相同，主要是移除反引号
        sql = re.sub(r'PRIMARY\s + KEY\s*\(\s*`([^`]+)`\s*\)',
                     r'PRIMARY KEY (\1)', sql, flags=re.IGNORECASE)

        return sql

    def _convert_table_indexes(self, sql: str) -> str:
        """转换表内索引定义"""
        # 转换KEY定义为INDEX
        sql = re.sub(r'\bKEY\s+`?([^`\s]+)`?\s*\(', r'INDEX \1 (', sql, flags=re.IGNORECASE)

        # 转换UNIQUE KEY
        sql = re.sub(r'UNIQUE\s + KEY\s+`?([^`\s]+)`?\s*\(',
                     r'UNIQUE INDEX \1 (', sql, flags=re.IGNORECASE)

        return sql

    def _convert_constraints(self, sql: str) -> str:
        """转换约束定义"""
        # KWDB目前对外键支持有限，可能需要移除或转换
        # 这里先保留，后续可以根据KWDB的具体支持情况调整

        # 移除反引号
        sql = re.sub(r'FOREIGN\s + KEY\s*\(\s*`([^`]+)`\s*\)',
                     r'FOREIGN KEY (\1)', sql, flags=re.IGNORECASE)

        sql = re.sub(r'REFERENCES\s+`([^`]+)`\s*\(\s*`([^`]+)`\s*\)',
                     r'REFERENCES \1 (\2)', sql, flags=re.IGNORECASE)

        return sql

    def _convert_on_duplicate_key(self, sql: str) -> str:
        """转换ON DUPLICATE KEY UPDATE为UPSERT语法"""
        # 这是一个复杂的转换，需要将INSERT ... ON DUPLICATE KEY UPDATE
        # 转换为KWDB的UPSERT语法

        # 简化处理：移除ON DUPLICATE KEY UPDATE部分
        # 实际应用中可能需要更复杂的逻辑
        sql = re.sub(r'ON\s + DUPLICATE\s + KEY\s + UPDATE.*$', '', sql, flags=re.IGNORECASE)

        self.logger.warning("检测到ON DUPLICATE KEY UPDATE语句，已移除该部分，可能需要手动调整")

        return sql

    def _cleanup_sql(self, sql: str) -> str:
        """清理SQL语句"""
        # 移除多余的空白
        sql = re.sub(r'\s+', ' ', sql)

        # 移除多余的逗号
        sql = re.sub(r',\s*,', ',', sql)
        sql = re.sub(r',\s*\)', ')', sql)

        # 移除空的括号内容，但保留函数调用的括号
        sql = re.sub(r'(?<!unique_rowid)\(\s*\)', '', sql)

        return sql.strip()

    def get_conversion_summary(self, original_statements: List[ParsedStatement],
                               converted_statements: List[ParsedStatement]) -> Dict[str, Any]:
        """获取转换摘要"""
        summary = {
            'total_original': len(original_statements),
            'total_converted': len(converted_statements),
            'skipped': len(original_statements) - len(converted_statements),
            'by_type': {}
        }

        # 按类型统计
        for stmt_type in StatementType:
            original_count = len([s for s in original_statements if s.statement_type == stmt_type])
            converted_count = len([s for s in converted_statements if s.statement_type == stmt_type])

            if original_count > 0:
                summary['by_type'][stmt_type.value] = {
                    'original': original_count,
                    'converted': converted_count,
                    'success_rate': converted_count / original_count * 100
                }

        return summary
